﻿Imports System.Data.SQLite
Public Class SqliteVbNetHelper
    '应用程序执行路径
    Public Shared APPdirPath As String = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetModules()(0).FullyQualifiedName)
    '本地库 查询当前本地库是否存在
    Public Shared SqliteDB As SqliteVbNetHelper
    Public Shared Sub CreateIns()
        SqliteDB = New SqliteVbNetHelper(APPdirPath, "DBCommon")
    End Sub
    ''' <summary>
    ''' 公共数据库连接对象
    ''' </summary>
    Private conn As New SQLiteConnection
    ''' <summary>
    ''' 数据库完整路径名
    ''' </summary>
    Private LocalPath As String

    ''' <summary>
    ''' 构造函数
    ''' </summary>
    ''' <param name="Path">数据库文件位置</param>
    ''' <param name="Name">数据库名</param>
    Sub New(ByVal Path As String, ByVal Name As String)
        LocalPath = Path + "\DataBase\" + Name + ".db3"
        If System.IO.Directory.Exists(Path + "\DataBase") = False Then
            System.IO.Directory.CreateDirectory(Path + "\DataBase")
        End If
        '是否创建表
        Dim CreateTabFlag As Boolean = False
        If System.IO.File.Exists(LocalPath) = False Then
            '创建数据库
            SQLiteConnection.CreateFile(LocalPath)
            '第一次创建数据库文件，同时需要创建表
            CreateTabFlag = True
        End If
        conn = New SQLiteConnection("Data Source=" + LocalPath + ";Pooling=false;FailIfMissing=true")
        '创建数据库中的表
        If CreateTabFlag = True Then
            '00.合同信息表
            Dim sqlStr As String = "CREATE TABLE ht_info (id INTEGER  PRIMARY KEY AUTOINCREMENT  NOT NULL,htbh  STRING NOT NULL,xmbh STRING,zbdlgs STRING,htmc STRING,htpy STRING,ghs STRING,hte DOUBLE,htqdrq  DATETIME,htysrq DATETIME,ghskpxx STRING,htlrrq  DATETIME, htlrr STRING,del_flag INTEGER  DEFAULT (0),jc_flag INTEGER  DEFAULT (0),scan_flag INTEGER  DEFAULT (0))"
            CreateDBTable(sqlStr)
            '01.设备信息表
            sqlStr = "CREATE TABLE sb_info ( " & _
                     "id       INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL," & _
                     "mc       STRING," & _
                     "sbpy       STRING," & _
                     "xhgg     STRING," & _
                     "sbsn     STRING," & _
                     "barcode  STRING," & _
                     "bp       STRING," & _
                     "zczh     STRING," & _
                     "syks     STRING," & _
                     "ghsmc    STRING," & _
                     "ghslxfs  STRING," & _
                     "cjmc     STRING," & _
                     "cjlxfs   STRING," & _
                     "ysrq     DATETIME," & _
                     "rzrq     DATETIME," & _
                     "zbq      STRING," & _
                     "sbsl     INTEGER," & _
                     "danjia    DOUBLE," & _
                     "memo_note STRING," & _
                     "sblb     STRING," & _
                     "tf_flag   INTEGER," & _
                     "jk_flag   INTEGER," & _
                     "wmgsmc    STRING," & _
                     "bgd      STRING," & _
                     "jyjyzm   STRING," & _
                     "xcjyrq     DATETIME," & _
                     "zybkzpjb   STRING," & _
                     "bf_flag  INTEGER," & _
                     "bfrq     DATETIME," & _
                     "bfwjph   STRING," & _
                     "htbh     STRING)"
            CreateDBTable(sqlStr)
            '02合同借阅管理
            sqlStr = "CREATE TABLE htjy_info (" & _
                     "id        INTEGER      PRIMARY KEY AUTOINCREMENT NOT NULL," & _
                     "htid      INTEGER     NOT NULL," & _
                     "jyr       STRING       NOT NULL," & _
                     "jysj      DATETIME     NOT NULL," & _
                     "ghsj      DATETIME)"
            CreateDBTable(sqlStr)
            '1.本地多媒体缓存表
            sqlStr = "Create Table multi_media_info(Id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,study_no varchar(50) NOT NULL,media_type integer NOT NULL,path VARCHAR(250) NOT NULL,filename VARCHAR(50) NOT NULL,memo_note VARCHAR(50) DEFAULT('') NOT NULL,Create_datetime datetime,del_flag integer DEFAULT(0) NOT NULL,hospital_code VARCHAR(50) DEFAULT('') NOT NULL,modality VARCHAR(50) DEFAULT('') NOT NULL,img_wz integer DEFAULT(0) NOT NULL,img_note VARCHAR(50) DEFAULT('') NOT NULL,timestamp BIGINT DEFAULT('0') NOT NULL)"
            CreateDBTable(sqlStr)
            '2.	人员表UserInfoTab
            sqlStr = "Create Table UserInfoTab(Id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE ,UserNum integer NOT NULL,UserName varchar(50) NOT NULL,Password VARCHAR(50) NOT NULL,UserType byte NOT NULL,DigitalSignature Blob)"
            CreateDBTable(sqlStr)
            '插入一条超级管理员
            sqlStr = "insert into UserInfoTab values(null,101,'admin','admin',1,null)"
            ExecuteNonQuery(sqlStr, Nothing)
            '3.	人员类型权限表UserTypeAuthorityTab
            sqlStr = "Create Table UserTypeAuthorityTab(Id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,UserType byte not null,TypeName varchar(50) not null)"
            CreateDBTable(sqlStr)
            '插入三条数据到表中
            sqlStr = "insert into UserTypeAuthorityTab values(null,1,'超级管理员')"
            ExecuteNonQuery(sqlStr, Nothing)
            sqlStr = "insert into UserTypeAuthorityTab values(null,2,'管理员')"
            ExecuteNonQuery(sqlStr, Nothing)
            sqlStr = "insert into UserTypeAuthorityTab values(null,3,'用户')"
            ExecuteNonQuery(sqlStr, Nothing)

        End If
    End Sub

    ''' <summary>
    ''' 打开数据库连接
    ''' </summary>
    Private Sub SqlOpenConn()
        If conn.State <> ConnectionState.Open Then
            conn.Open()
        End If
    End Sub


    ''' <summary>
    ''' 关闭数据库连接
    ''' </summary>
    Public Sub SqlCloseConn()
        If conn.State = ConnectionState.Open Then
            conn.Close()
        End If
    End Sub
    ''' <summary>
    ''' 创建表
    ''' </summary>
    Public Sub CreateDBTable(ByVal sql As String)
        SqlOpenConn()
        Dim cmd As SQLiteCommand = New SQLiteCommand(sql, conn)
        cmd.CommandText = sql
        cmd.ExecuteNonQuery()
        SqlCloseConn()
    End Sub
    ''' <summary>
    ''' 获取数据读取集
    ''' </summary>
    Public Function GetSqlReader(ByVal StrSql As String, ByVal SqlParas As IList(Of SQLiteParameter)) As SQLiteDataReader
        Dim cmd As SQLiteCommand = New SQLiteCommand(StrSql, conn)
        If IsNothing(SqlParas) = False AndAlso SqlParas.Count <> 0 Then
            For Each para As SQLiteParameter In SqlParas
                cmd.Parameters.Add(para)
            Next
        End If
        SqlOpenConn()
        Return cmd.ExecuteReader(CommandBehavior.CloseConnection)
    End Function

    ''' <summary>
    ''' 获取数据读取集
    ''' </summary>
    Public Function GetSqlReader(ByVal StrSql As String) As Boolean
        Dim RetBool As Boolean = False
        Dim cmd As SQLiteCommand = New SQLiteCommand(StrSql, conn)
        SqlOpenConn()
        Dim insRead As SQLiteDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        If insRead.HasRows Then
            RetBool = True
        End If
        SqlCloseConn()
        Return RetBool
    End Function
    ''' <summary>
    ''' 获取数据表
    ''' </summary>
    Public Function GetDataTable(ByVal StrSql As String, ByVal SqlParas As IList(Of SQLiteParameter)) As DataTable
        SqlOpenConn()
        Dim cmd As SQLiteCommand = New SQLiteCommand(StrSql, conn)
        If IsNothing(SqlParas) = False AndAlso SqlParas.Count <> 0 Then
            For Each para As SQLiteParameter In SqlParas
                cmd.Parameters.Add(para)
            Next
        End If
        Dim sa As New SQLiteDataAdapter(cmd)
        Dim mytable As New System.Data.DataTable
        sa.Fill(mytable)
        SqlCloseConn()
        Return mytable
    End Function

    Public Function GetDataTable(ByVal StrSql As String) As DataTable
        SqlOpenConn()
        Dim cmd As SQLiteCommand = New SQLiteCommand(StrSql, conn)
        Dim sa As New SQLiteDataAdapter(cmd)
        Dim mytable As New System.Data.DataTable
        sa.Fill(mytable)
        SqlCloseConn()
        Return mytable
    End Function

    ''' <summary>
    ''' 返回第一行第一列的值
    ''' </summary>
    Public Function ExecuteScalar(ByVal StrSql As String) As Object
        Dim cmd As SQLiteCommand = New SQLiteCommand(StrSql, conn)
        SqlOpenConn()
        Return cmd.ExecuteScalar()
    End Function
    ''' <summary>
    ''' 执行增删改
    ''' </summary>
    Public Function ExecuteNonQuery(ByVal StrSql As String, ByVal SqlParas As IList(Of SQLiteParameter)) As Integer
        SqlOpenConn()
        Dim tran As SQLiteTransaction = conn.BeginTransaction
        Dim cmd As SQLiteCommand = New SQLiteCommand(StrSql, conn)
        If IsNothing(SqlParas) = False AndAlso SqlParas.Count <> 0 Then
            For Each para As SQLiteParameter In SqlParas
                cmd.Parameters.Add(para)
            Next
        End If
        Dim affectedRows As Integer = 0
        Try
            affectedRows = cmd.ExecuteNonQuery()
            tran.Commit()
        Catch ex As Exception
            tran.Rollback()
        End Try
        SqlCloseConn()
        Return affectedRows
    End Function
    ''' <summary>
    ''' 执行增删改
    ''' </summary>
    Public Function ExecuteNonQuery(ByVal StrSql As String) As Integer
        SqlOpenConn()
        Dim tran As SQLiteTransaction = conn.BeginTransaction
        Dim cmd As SQLiteCommand = New SQLiteCommand(StrSql, conn)
        Dim affectedRows As Integer = 0
        Try
            affectedRows = cmd.ExecuteNonQuery()
            tran.Commit()
        Catch ex As Exception
            tran.Rollback()
        End Try
        SqlCloseConn()
        Return affectedRows
    End Function
    ''' <summary>
    ''' 用户身份验证
    ''' </summary>
    ''' <param name="name">用户名</param>
    ''' <param name="pwd">密码</param>
    ''' <returns>true代表查询存在此用户且密码正确；成功，2代表失败</returns>
    ''' <remarks></remarks>
    Public Function UserCheck(ByVal name As String, ByVal pwd As String) As Boolean
        Dim strsql As String = "select * from UserInfoTab where UserName=@usr and Password=@pwd"
        Dim sqliteparas As SQLiteParameter() = New SQLiteParameter() {New SQLiteParameter("@usr", name), New SQLiteParameter("@pwd", pwd)}
        Dim rader As SQLiteDataReader = GetSqlReader(strsql, sqliteparas)
        If Not IsNothing(rader) AndAlso rader.HasRows = True Then
            SqlCloseConn()
            Return True
        End If
        Return False
    End Function
    ''' <summary>
    ''' 查询当前系统下是否有此用户名的一个记录
    ''' </summary>
    ''' <param name="name">用户名</param>
    ''' <returns>true代表查询存在此用户</returns>
    ''' <remarks></remarks>
    Public Function UserHaveOne(ByVal name As String) As Boolean
        Dim strsql As String = "select * from UserInfoTab where UserName=@usr"
        Dim sqliteparas As SQLiteParameter() = New SQLiteParameter() {New SQLiteParameter("@usr", name)}
        Dim rader As SQLiteDataReader = GetSqlReader(strsql, sqliteparas)
        If Not IsNothing(rader) AndAlso rader.HasRows = True Then
            SqlCloseConn()
            Return True
        End If
        Return False
    End Function
    ''' <summary>
    ''' 更新密码
    ''' </summary>
    ''' <param name="name">用户名</param>
    ''' <param name="pwd">密码</param>
    ''' <returns>true代表查询存在此用户且密码正确；成功，2代表失败</returns>
    ''' <remarks></remarks>
    Public Function UpdateUserPwd(ByVal name As String, ByVal pwd As String) As Integer
        Dim strsql As String = "update UserInfoTab set Password=@pwd where  UserName=@usr"
        Dim sqliteparas As SQLiteParameter() = New SQLiteParameter() {New SQLiteParameter("@pwd", pwd), New SQLiteParameter("@usr", name)}
        Dim rader As Integer = ExecuteNonQuery(strsql, sqliteparas)
        If rader = 1 Then
            SqlCloseConn()
            Return 1
        End If
        Return 0
    End Function
    ''' <summary>
    ''' 保存临时文件到本地数据库中
    ''' </summary>
    ''' <param name="ListRealDataFile">实时数据文件信息集合</param>
    ''' <remarks></remarks>
    Public Function InsertRealDataFile(ByVal ListRealDataFile As List(Of Object), Optional timestamp As String = "0") As Integer
        Dim sqlstr As String = "Insert into multi_media_info(id,study_no,media_type,path,filename,hospital_code,modality,Create_datetime,timestamp) values(NUll,@study_no,@media_type,@path,@filename,@hospital_code,@modality,@Create_datetime,@timestamp)"
        Dim sqliteRealDataFile As SQLiteParameter() = New SQLiteParameter() {New SQLiteParameter("@study_no", ListRealDataFile.Item(0).ToString()), New SQLiteParameter("@media_type", ListRealDataFile.Item(1).ToString()), New SQLiteParameter("@path", Convert.ToString(ListRealDataFile.Item(2))), New SQLiteParameter("@filename", Convert.ToString(ListRealDataFile.Item(3))), New SQLiteParameter("@hospital_code", Convert.ToString(ListRealDataFile.Item(4))), New SQLiteParameter("@modality", Convert.ToString(ListRealDataFile.Item(5))), New SQLiteParameter("@Create_datetime", Convert.ToString(ListRealDataFile.Item(6))), New SQLiteParameter("@timestamp", timestamp)}
        Dim num As Integer = ExecuteNonQuery(sqlstr, sqliteRealDataFile)
        Return num
    End Function

    Public Sub UpdateWZBZ(ByVal ListRealDataFile As List(Of Object))
        Dim sqlstr As String = "update multi_media_info set img_wz=0,img_note='' where img_wz=@img_wz and study_no=@study_no and hospital_code=@hospital_code"
        Dim sqliteRealDataFile As SQLiteParameter() = New SQLiteParameter() {New SQLiteParameter("@img_wz", ListRealDataFile.Item(0).ToString()), New SQLiteParameter("@study_no", Convert.ToString(ListRealDataFile.Item(3).ToString())), New SQLiteParameter("@hospital_code", Convert.ToString(ListRealDataFile.Item(4).ToString()))}
        Dim num As Integer = ExecuteNonQuery(sqlstr, sqliteRealDataFile)
        '
        sqlstr = "update multi_media_info set img_wz=@img_wz,img_note=@img_note where filename=@filename and study_no=@study_no and hospital_code=@hospital_code"
        sqliteRealDataFile = New SQLiteParameter() {New SQLiteParameter("@img_wz", ListRealDataFile.Item(0).ToString()), New SQLiteParameter("@img_note", ListRealDataFile.Item(1).ToString()), New SQLiteParameter("@filename", Convert.ToString(ListRealDataFile.Item(2).ToString())), New SQLiteParameter("@study_no", Convert.ToString(ListRealDataFile.Item(3).ToString())), New SQLiteParameter("@hospital_code", Convert.ToString(ListRealDataFile.Item(4).ToString()))}
        num = ExecuteNonQuery(sqlstr, sqliteRealDataFile)
    End Sub
    Public Sub UpdateWZ(ByVal ListRealDataFile As List(Of Object))
        Dim sqlstr As String = "update multi_media_info set img_wz=0 where img_wz=@img_wz and study_no=@study_no and hospital_code=@hospital_code"
        Dim sqliteRealDataFile As SQLiteParameter() = New SQLiteParameter() {New SQLiteParameter("@img_wz", ListRealDataFile.Item(0).ToString()), New SQLiteParameter("@study_no", Convert.ToString(ListRealDataFile.Item(2).ToString())), New SQLiteParameter("@hospital_code", Convert.ToString(ListRealDataFile.Item(3).ToString()))}
        Dim num As Integer = ExecuteNonQuery(sqlstr, sqliteRealDataFile)
        '
        sqlstr = "update multi_media_info set img_wz=@img_wz where filename=@filename and study_no=@study_no and hospital_code=@hospital_code"
        sqliteRealDataFile = New SQLiteParameter() {New SQLiteParameter("@img_wz", ListRealDataFile.Item(0).ToString()), New SQLiteParameter("@filename", Convert.ToString(ListRealDataFile.Item(1).ToString())), New SQLiteParameter("@study_no", Convert.ToString(ListRealDataFile.Item(2).ToString())), New SQLiteParameter("@hospital_code", Convert.ToString(ListRealDataFile.Item(3).ToString()))}
        num = ExecuteNonQuery(sqlstr, sqliteRealDataFile)
    End Sub

    Public Function DeleteTX(ByVal ListRealDataFile As List(Of Object)) As Integer
        Dim sqlstr As String = "delete from multi_media_info  where filename=@filename and study_no=@study_no and hospital_code=@hospital_code"
        Dim sqliteRealDataFile As SQLiteParameter() = New SQLiteParameter() {New SQLiteParameter("@filename", ListRealDataFile.Item(0).ToString()), New SQLiteParameter("@study_no", Convert.ToString(ListRealDataFile.Item(1).ToString())), New SQLiteParameter("@hospital_code", Convert.ToString(ListRealDataFile.Item(2).ToString()))}
        Dim num As Integer = ExecuteNonQuery(sqlstr, sqliteRealDataFile)
        Return num
    End Function
End Class
